package com.example.springboot.mqtt.task;

import cn.hutool.core.lang.Console;
import cn.hutool.core.util.IdUtil;
import cn.hutool.poi.excel.ExcelUtil;
import cn.hutool.poi.excel.sax.handler.RowHandler;
import com.baomidou.mybatisplus.core.conditions.query.LambdaQueryWrapper;
import com.example.springboot.mqtt.entity.*;
import com.example.springboot.mqtt.serivice.*;
import com.example.springboot.mqtt.util.RedisUtil;
import lombok.extern.slf4j.Slf4j;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.scheduling.annotation.Scheduled;
import org.springframework.web.bind.annotation.RestController;

import java.io.File;
import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

@RestController
@Slf4j
public class cnpcData {
    @Autowired
    private RedisUtil redisUtil;
    @Autowired
    private IProductClassService productClassService;
    @Autowired
    private IProductService productService;
    @Autowired
    private IStoreService storeService;
    @Autowired
    private IStoreCityService storeCityService;
    @Autowired
    private IStoreOrderService storeOrderService;
//    @Scheduled(cron = "0/10 * * * * ?")
    public void readExcle(){
        log.info("遍历excel文件");
        File directory = new File("F:\\cnpcExcel\\"); // 指定目录的路径
        System.out.println(directory);
        List<File> excelFiles = new ArrayList<>();
        File[] files = directory.listFiles();
        if (files != null) {
            for (File file : files) {
                if (!file.isDirectory()) {
                    if (file.getName().endsWith(".xls") || file.getName().endsWith(".xlsx")) {
                        excelFiles.add(file);
                    }
                }
            }
        }

        for (File file : excelFiles) {
            // 在这里可以对Excel文件进行处理，比如读取内容等操作
            String fileName = file.getName();
            String suffix = fileName.substring(fileName.lastIndexOf("."));
            String filePath = "F:\\cnpcExcel\\processed\\";
            File fileDir = new File(filePath);
            if (!fileDir.exists()){
                fileDir.mkdirs();
            }
            String filePathName = filePath + fileName;
//            File filePathNameDir = new File(filePathName);
//            if(filePathNameDir.exists()){
//               log.info("文件："+fileName+"已存在，请勿重复读取");
//                return;
//            }
            file.renameTo(new File(filePathName));
            //读取excel文件内容
            ExcelUtil.readBySax(filePathName, 0, createRowHandler());

        }
    }

    private RowHandler createRowHandler() {
        return new RowHandler() {
            @Override
            public void handle(int sheetIndex, long rowIndex, List<Object> rowlist) {
                // 获取当前时间
                LocalDateTime now = LocalDateTime.now();
                // 定义时间的格式
                DateTimeFormatter formatter = DateTimeFormatter.ofPattern("yyyyMMdd");
                // 格式化时间
                String formattedDateTime = now.format(formatter);

                String key = "data:list";
                StringBuilder sb = new StringBuilder();
                for (Object value : rowlist) {
                    if(value== null){
                        value = "NaN";
                    }
                    sb.append(value).append("##");
                }
                redisUtil.lSet(key,sb.toString());
                log.info("[{}] [{}] {}", sheetIndex, rowIndex, rowlist);
            }
        };
    }

//    @Scheduled(cron = "0/10 * * * * ?")
    public void getDataFromRedis() {
        log.info("从redis的datalist列表中读取数据");
        try {
            while (true) {
                Object res = redisUtil.lPop("data:list");
                System.out.println(res);
                if (res != null) {
                    String[] arr = res.toString().split("##");
                    if (arr[0].equals("二级类（品类）")) continue;
                    for (int i = 0; i < arr.length; i++) {
                        if (arr[i].equals("NaN")) {
                            arr[i] = "0";
                        }
                    }
                    //商品分类id
                    long productClassId = 0L;
                    if (arr[0] != null && !arr[0].equals("NaN")) {
                        ProductClass productClassInfo = productClassService.getOne(new LambdaQueryWrapper<ProductClass>().eq(ProductClass::getCode, arr[0]));
                        if (productClassInfo == null) {
                            ProductClass productClass = new ProductClass();
                            productClass.setId(IdUtil.getSnowflake().nextId());
                            productClass.setCode(arr[0]);
                            productClass.setClassName(arr[1]);
                            productClassService.save(productClass);
                            productClassId = productClass.getId();
                        } else {
                            productClassId = productClassInfo.getId();
                        }
                    }

                    //商品id
                    long productId = 0L;
                    if (arr[2] != null && !arr[2].equals("NaN")) {
                        Product productInfo = productService.getOne(new LambdaQueryWrapper<Product>().eq(Product::getCode, arr[2]));
                        if (productInfo == null) {
                            Product product = new Product();
                            product.setId(IdUtil.getSnowflake().nextId());
                            product.setCode(arr[2]);
                            product.setProductName(arr[3]);
                            productService.save(product);
                            productId = product.getId();
                        } else {
                            productId = productInfo.getId();
                        }
                    }

                    //便利店所属市id
                    long storeCityId = 0L;
                    if (arr[4] != null && !arr[4].equals("NaN")) {
                        StoreCity storeCityInfo = storeCityService.getOne(new LambdaQueryWrapper<StoreCity>().eq(StoreCity::getCityName, arr[4]));
                        if (storeCityInfo == null) {
                            StoreCity storeCity = new StoreCity();
                            storeCity.setId(IdUtil.getSnowflake().nextId());
                            storeCity.setCityName(arr[4]);
                            storeCityService.save(storeCity);
                            storeCityId = storeCity.getId();
                        } else {
                            storeCityId = storeCityInfo.getId();
                        }
                    }

                    //便利店id
                    long storeId = 0L;
                    if (arr[5] != null && !arr[5].equals("NaN") && !arr[5].equals("结果")) {
                        Store storeInfo = storeService.getOne(new LambdaQueryWrapper<Store>().eq(Store::getCode, arr[5]));
                        if (storeInfo == null) {
                            Store store = new Store();
                            store.setId(IdUtil.getSnowflake().nextId());
                            store.setCode(arr[5]);
                            store.setStoreName(arr[6]);
                            store.setStoreCityId(storeCityId);
                            storeService.save(store);
                            storeId = store.getId();
                        } else {
                            storeId = storeInfo.getId();
                        }
                    }

                    //写入订单信息
                    /**
                     * 1、如果表中库存数量和金额跟数据不一致，则直接写入订单表
                     * 2、如果库存数量和金额都为0，则直接写入订单表
                     * 3、库存数量 = 购进数量+配送数量-退货数量-销售数量+调入数量-调出数量+盘盈数量-盘亏数量-报废数量
                     * 4、金额 = 购进金额+配送金额-退货金额-销售金额+调入金额-调出金额+盘盈金额-盘亏金额-报废金额
                     * 5、写入销售成本、销售收入
                     * 6、结合商品分类id、商品id、便利店所属市id、便利店id和订单数据写入订单表
                     */
                    //查询订单表中分类id、商品id和便利店所属市id、便利店id是否存在
                    StoreOrder orderInfo = storeOrderService.getOne(new LambdaQueryWrapper<StoreOrder>()
                            .eq(StoreOrder::getProductId, productId)
                            .eq(StoreOrder::getStoreCityId, storeCityId)
                            .eq(StoreOrder::getStoreId, storeId)
                            .orderByDesc(StoreOrder::getCreateTime)
                            .last("limit 1")
                    );
                    //1、如果表中库存数量和金额跟数据不一致，则直接写入订单表
                    int count = 0;
                    double price = 0;
                    int type = 0;
                    if (orderInfo != null) {
                        if (orderInfo.getCount()== Integer.parseInt(arr[30]) && orderInfo.getPrice()== Double.parseDouble(arr[31])
                                && orderInfo.getSalesCost()== Double.parseDouble(arr[18]) && orderInfo.getSalesIncome()== Double.parseDouble(arr[19])){
                            log.error(orderInfo.getId() + "重复数据！");
                            continue;
                        }
                        //判断库存数量与金额是否一致
                        if (orderInfo.getCount() != Integer.parseInt(arr[8]) || orderInfo.getPrice() != Double.parseDouble(arr[9])) {
                            log.error(orderInfo.getCount() + " - " + Integer.parseInt(arr[8]));
                            log.error(orderInfo.getPrice() + " - " + Double.parseDouble(arr[9]));
                            log.error(orderInfo.getId() + "期初库存数量或期初库存价值异常！");
                            //重新写回redis缓存
                            redisUtil.lSet("data:error",res);
                            continue;
                        }
                        // 3、库存数量 = 期初库存数量+购进数量-配送数量-退货数量-销售数量+调入数量-调出数量+盘盈数量-盘亏数量-报废数量
                        count = orderInfo.getCount() + Math.abs(Integer.parseInt(arr[10])) - Math.abs(Integer.parseInt(arr[12])) - Math.abs(Integer.parseInt(arr[14])) - Math.abs(Integer.parseInt(arr[16])) + Math.abs(Integer.parseInt(arr[20])) - Math.abs(Integer.parseInt(arr[22])) + Math.abs(Integer.parseInt(arr[24])) - Math.abs(Integer.parseInt(arr[26])) - Math.abs(Integer.parseInt(arr[28]));
                        //4、金额 = 期初库存价值+购进金额-配送金额-退货金额-销售金额+调入金额-调出金额+盘盈金额-盘亏金额-报废金额
                        price = orderInfo.getPrice() + Math.abs(Double.parseDouble(arr[11])) - Math.abs(Double.parseDouble(arr[13])) - Math.abs(Double.parseDouble(arr[15])) - Math.abs(Double.parseDouble(arr[18])) + Math.abs(Double.parseDouble(arr[21])) - Math.abs(Double.parseDouble(arr[23])) + Math.abs(Double.parseDouble(arr[25])) - Math.abs(Double.parseDouble(arr[27])) - Math.abs(Double.parseDouble(arr[29]));
                        //四舍五入，保留小数点后两位
                        price = new BigDecimal(price).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();
                        //判断期末库存数量与金额是否一致
                        if (count != Integer.parseInt(arr[30]) || price != Double.parseDouble(arr[31])) {
                            log.error(count + " - " + Integer.parseInt(arr[30]));
                            log.error(price + " - " + Double.parseDouble(arr[31]));
                            log.error(orderInfo.getId() + "期末库存数量或期末库存价值异常！");
                            //重新写回redis缓存
                            redisUtil.lSet("data:error",res);
                            continue;
                        }
                    } else {
                        // 3、库存数量 = 期初库存数量+购进数量-配送数量-退货数量-销售数量+调入数量-调出数量+盘盈数量-盘亏数量-报废数量
                        count = Math.abs(Integer.parseInt(arr[8])) + Math.abs(Integer.parseInt(arr[10])) - Math.abs(Integer.parseInt(arr[12])) - Math.abs(Integer.parseInt(arr[14])) - Math.abs(Integer.parseInt(arr[16])) + Math.abs(Integer.parseInt(arr[20])) - Math.abs(Integer.parseInt(arr[22])) + Math.abs(Integer.parseInt(arr[24])) - Math.abs(Integer.parseInt(arr[26])) - Math.abs(Integer.parseInt(arr[28]));
                        //4、金额 = 期初库存价值+购进金额-配送金额-退货金额-销售金额+调入金额-调出金额+盘盈金额-盘亏金额-报废金额
                        price = Math.abs(Double.parseDouble(arr[9])) + Math.abs(Double.parseDouble(arr[11])) - Math.abs(Double.parseDouble(arr[13])) - Math.abs(Double.parseDouble(arr[15])) - Math.abs(Double.parseDouble(arr[18])) + Math.abs(Double.parseDouble(arr[21])) - Math.abs(Double.parseDouble(arr[23])) + Math.abs(Double.parseDouble(arr[25])) - Math.abs(Double.parseDouble(arr[27])) - Math.abs(Double.parseDouble(arr[29]));
                        //判断期末库存数量与金额是否一致
                        //四舍五入，保留小数点后两位
                        price = new BigDecimal(price).setScale(2, BigDecimal.ROUND_HALF_UP).doubleValue();
                        if (count != Integer.parseInt(arr[30]) || price != Double.parseDouble(arr[31])) {
                            log.error(count + " - " + Integer.parseInt(arr[30]));
                            log.error(price + " - " + Double.parseDouble(arr[31]));
                            log.error("期末库存数量或期末库存价值异常！- 商品id:" + productId + " 城市id:" + storeCityId + " 便利店id:" + storeId);
                            //重新写回redis缓存
                            redisUtil.lSet("data:error",res);
                            continue;
                        }
                    }


                    StoreOrder storeOrder = new StoreOrder();
                    storeOrder.setId(IdUtil.getSnowflake().nextId());
                    storeOrder.setProductId(productId);
                    storeOrder.setStoreCityId(storeCityId);
                    storeOrder.setStoreId(storeId);
                    storeOrder.setType(type);
                    storeOrder.setCount(count);
                    storeOrder.setPrice(price);
                    //5、写入销售成本、销售收入
                    storeOrder.setSalesCost(Double.parseDouble(arr[18]));
                    storeOrder.setSalesIncome(Double.parseDouble(arr[19]));
                    storeOrder.setCreateTime(new Date());
                    System.out.println(storeOrder);
                    storeOrderService.save(storeOrder);
                } else {
                    return;
                }
            }
        }catch (Exception e) {
            e.printStackTrace();
        }
    }
}
